package phan.connection;


import com.sun.org.apache.bcel.internal.generic.AALOAD;
import java.awt.List;
import java.sql.*;
import java.util.Properties;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.mail.*;
import javax.mail.internet.*;
import java.sql.*;
import java.io.*;
import java.util.Calendar;
import javax.naming.spi.DirStateFactory.Result;
import javax.servlet.http.HttpSession;
import org.apache.commons.fileupload.*;
import org.apache.tomcat.util.http.fileupload.FileUpload;
import org.apache.commons.fileupload.DiskFileUpload;
import org.apache.commons.fileupload.disk.*;
import org.apache.commons.fileupload.servlet.*;
import javax.servlet.*;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

/**
 *
 * @author Bui Trung Dung
 * Classe est utilisé si on veux connecter a la base de donnée.
 */
public class MySQLDBConnector {

    private Connection connection;
//connecter a la base de donnée.

    public MySQLDBConnector() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            String url = "jdbc:mysql://localhost:3306/lostobjet";
            connection = DriverManager.getConnection(url, "root", "19091982");

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public Connection connect() {
        return connection;
    }
//méthode pour sortir base de donnée

    public void close() {
        try {
            connection.close();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }
//méthode pour tester le user est existe ou pas
    //data donnée: String username
    //data sorti: true ou false

    public boolean checkUserName(String username) {
        try {
            Statement stmt = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
            String sql = "select UserLogin from usertable ";
            stmt.executeQuery(sql);
            ResultSet srs = stmt.getResultSet();
            while (srs.next()) {
                String name = srs.getString("UserLogin").toString();
                System.out.print("name la "+ name);
                if (username.equals(name)) {
                    // System.out.print(" UserName existe");
                    return true;

                }
            }
            return false;
        } catch (Exception ex) {
            ex.printStackTrace();
            return false;
        }
    }


//method pour envoie un email automatiquement
//datas entrées: host-mail, mail de envoyer, mail de recevoir, subjet de mail, messege de mail
//data sorti: true ou false
    public boolean sendMail(
            String mail_server_host,
            String email_from,
            String email_to,
            String subject,
            String message) {
        try {


            String username = "";
            String password = "";
            Properties pros = new Properties();
            pros.put("mail.smtp.host", mail_server_host);
            Session session = Session.getDefaultInstance(pros);
            session.setDebug(true);


            MimeMessage _message = new MimeMessage(session);
            _message.setFrom(new InternetAddress(email_from));
            _message.addRecipient(Message.RecipientType.TO, new InternetAddress(email_to));
            _message.setSubject(subject);
            _message.setText(message);
            //MimeMultipart multipart = new MimeMultipart("related");
/* setContent thi ko co setText
             * SetContent co the thoai mai cho the html
            // first part  (the html)
            BodyPart messageBodyPart = new MimeBodyPart();
            String htmlText = "<H1>Hello</H1>";
            messageBodyPart.setContent(htmlText, "text/html");

            // add it
            multipart.addBodyPart(messageBodyPart);

            _message.setContent(multipart);*/

            Transport tr = session.getTransport("smtp");
            tr.connect(mail_server_host, username, password);
            _message.saveChanges();

            // tr.send(message);
            /** Genere l'erreur. Avec l authentification, oblige d utiliser sendMessage meme pour une seule adresse... */
            tr.sendMessage(_message, _message.getAllRecipients());
            tr.close();
            return true;
        } catch (Exception e) {
            return false;
        }
    }

//le client apres faire inscription, il faut activer son comple par son email,
    //apres activer le client devient client visiteur
    //data entrée: UserLogin
    //data sorti:true ou false
    //false: UserLogin n'existe pas ou avoir autre problème
    //true: UserLogin est activé
    public boolean ActiveCompte(String login) {
        try {
            Statement stmt = connection.createStatement();
            ResultSet srs = stmt.executeQuery("SELECT UserValide,UserPrenom FROM usertable where UserLogin=" + "'" + login + "'");
            boolean valide = false;
            String UserValide = "";
            while (srs.next()) {
                UserValide = srs.getString("UserValide");
                System.out.print(UserValide);
            }
            if (UserValide.equals("0")) {
                stmt.executeUpdate("Update usertable set UserValide=1 where userlogin='" + login + "'");
                valide = true;
            } else {
                valide = false;
            }
            close();
            return valide;
        } catch (Exception ex) {
            ex.printStackTrace();
            return false;

        }
    }
    //active paiementvalide trong bang paiementtable
    // just tester

    public boolean MailPaiement(String mail_server_host, String email_from, String email_to, String login, String _StockageName, String _serie) {
        try {

            String username = "";
            String password = "";
            Properties pros = new Properties();
            pros.put("mail.smtp.host", mail_server_host);
            Session session = Session.getDefaultInstance(pros);
            session.setDebug(true);
            String title = "Bienvenue sur VotreSite,\nPour activer votre compte, veuillez cliquer sur le lien ci dessous\nou copier/coller dans votre navigateur internet.\n";
            String lien = "http://192.168.0.12:8080/PHAN/LostOB/ActifCompte.jsp?login=" + login + "&StockageName=" + _StockageName + "&serie=" + _serie;
            String send = title + lien;

            MimeMessage _message = new MimeMessage(session);
            _message.setFrom(new InternetAddress(email_from));
            _message.addRecipient(Message.RecipientType.TO, new InternetAddress(email_to));
            _message.setSubject("Activer Paiement");
            _message.setText(send);
            // _message.set
            // _message.setContent(_message);

            Transport tr = session.getTransport("smtp");
            tr.connect(mail_server_host, username, password);
            _message.saveChanges();

            // tr.send(message);
            /** Genere l'erreur. Avec l authentification, oblige d utiliser sendMessage meme pour une seule adresse... */
            tr.sendMessage(_message, _message.getAllRecipients());
            tr.close();

            boolean valide = true;
            return valide;
        } catch (Exception e) {

            System.out.print(e);
            return false;
        }
    }

    //vérifier User avec son UserLogin et son passwd pour savoir son role
    //datas entrées: UserLogin et UserPassword
    //data sorti: int
    //1.client visiteur
    //2.client abonnée
    //3.adm
    //4. client n'active jamais son compte
    public int checkUser(String username, String password) {
        try {
            int valide = 0;
            Statement stmt = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
            ResultSet srs = stmt.executeQuery("SELECT UserLogin, UserPassword, UserRoleID,UserValide FROM UserTable where UserLogin='" + username + "' and UserPassword ='" + password + "'");
            System.out.print("chay ddddddddddddddddddddeeeeeeeeeee");
            System.out.print("\n" + username);
            while (srs.next()) {
                String name = srs.getString("UserLogin");
                String pass = srs.getString("UserPassword");
                int UserRoleID = srs.getInt("UserRoleID");
                String UserValide = srs.getString("UserValide");
                System.out.print(" user valide" + UserValide);
                if (UserValide.equals("0")) {
                    valide = 4;
                } else {
                    if ((username.equals(name)) && (password.equals(pass)) && (UserRoleID == 1)) {
                        System.out.print("day nhe ");
                        valide = UserRoleID;
                    }
                    if ((username.equals(name)) && (password.equals(pass)) && (UserRoleID == 2)) {
                        valide = UserRoleID;
                    }
                    if ((username.equals(name)) && (password.equals(pass)) && (UserRoleID == 3)) {
                        valide = UserRoleID;
                    }
                }
            }
            return valide;
        } catch (Exception ex) {
            ex.printStackTrace();
            return 0;
        }
    }
//prendre UserID
//tester username et password dans base de donnée
//Si exite pas boolean= false
// Dans tous autres cas, boolean = true

    public int getUserID(String username) {
        try {
            Statement stmt = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
            ResultSet srs = stmt.executeQuery("SELECT  * FROM usertable");
            int userID = 0;
            while (srs.next()) {
                String name = srs.getString("UserLogin");

                if ((username.equals(name))) {
                    System.out.print("ddddddddddddddd?");
                    userID = srs.getInt("userID");
                    return userID;
                }
            }
            return userID;
        } catch (Exception ex) {
            ex.printStackTrace();
            return 0;
        }
    }

    //check condition et insert Objet vers database

    public boolean InserElement(String login) {
        try {
            Statement stmt = connection.createStatement();
            ResultSet srs = stmt.executeQuery("SELECT UserValide,UserPrenom FROM usertable where UserLogin=" + "'" + login + "'");
            boolean valide = false;
            String UserValide = "";
            while (srs.next()) {
                UserValide = srs.getString("UserValide");
                System.out.print(UserValide);
            }
            if (UserValide.equals("0")) {
                stmt.executeUpdate("Update usertable set UserValide=1 where userlogin=" + "'" + login + "'");
                valide = true;
            } else {
                valide = false;
            }
            close();
            return valide;
        } catch (Exception ex) {
            ex.printStackTrace();
            return false;

        }
    }
    ///Insert Element to database
    ResultSet srs = null;

    public int UpdateImageElement(String Path, int StockID) throws SQLException {
        int valide = 0;
        Statement stmt = connection.createStatement();

        valide = stmt.executeUpdate("Update elementtable set ElementImage1=" + "'" + Path + "'where StockID=" + "'" + StockID + "'");
        close();
        return valide;

    }
    public int StockID;
    public int MaxElement;

    //  Ham Insert Objet dung cho client ABN can phai bo sung : xwet dieu kien Date xem Tai khoan con gia tri su dung hay khong
    public int InserObjet(HttpSession session, ElementBean f, String StockName) {
        try {
            int valide = 0;


            //lay stockID theo UserID va StockName trong bang Paiementtable
            // Chu y la StockName co the trung tuy tung nguoi dung nen phai co UserID
            //check dieu kien o bang Paiement
            //DateDebut, DateFin kiem tra sau
            int UserID = Integer.parseInt(session.getAttribute("UserID").toString());
            String UserRoleID = session.getAttribute("UserRoleID").toString();
            System.out.print("test sesson UserRoleID " + UserRoleID);
            System.out.print("test sesson userID " + UserID);
            if (UserRoleID.equals("2")) {
                Statement stmt = connection.createStatement();
                System.out.print("vao insertObjet");
                String sqltest = "SELECT  StockID,ValidePaiement,DateDebut,DateFin,Quantite,MaxElements FROM PaiementTable INNER JOIN UserTable ON PaiementTable.UserID = UserTable.UserID ";
                ResultSet srs = stmt.executeQuery(sqltest + "where paiementtable.UserID=" + "'" + UserID + "'and StockName =" + "'" + StockName + "'");
                int StockID = 0;

                String ValidePaiement = "";
                Date DateDebut = null;
                Date DateFin = null;
                int Quantite = 0;
                while (srs.next()) {
                    StockID = srs.getInt("StockID");
                    setStockID(StockID);
                    ValidePaiement = srs.getString("ValidePaiement");
                    DateDebut = srs.getDate("DateDebut");
                    DateFin = srs.getDate("DateFin");
                    Quantite = srs.getInt("Quantite");
                    MaxElement = srs.getInt("MaxElements");
                    System.out.print("StockID= " + StockID);
                    System.out.print("ValidePaiement= " + ValidePaiement);
                    System.out.print("DateDebut= " + DateDebut);
                    System.out.print("DateFin= " + DateFin);
                    // Date using =DateFin-DateDebut;
                    //System.out.print("Date "+DateFin);
                    System.out.print("Quantite= " + Quantite);
                    System.out.print("MaxElement= " + MaxElement);
                }
                if (ValidePaiement.equals("1")) {
                    if (Quantite < MaxElement) {
                        Quantite = Quantite + 1;
                        //update truong Quantite trong bang paiement len 1

//19mai2009
                        Catalog cata = new Catalog(f.getCatalog());
                        int i = cata.getCatalog();
             String an=f.getAn();
            String jour=f.getJour();
            String mois=f.getMois();
            CheckString convert= new CheckString();
            String DateAchat=convert.convertDateAchat(an, mois, jour);
                        System.out.print("Catalog name " + f.getCatalog());
                        System.out.print("i= " + i);
                        String insertElement = "insert into elementtable(ElementNom,StockID,serie,ElementImage1,ElementDateInscription,ElementModel,ElementImage2,ElementEtat,ElementControl,CategorieID,ElementDateAchat) values (?,?,?,?,?,?,?,?,?,?,?)";
                        PreparedStatement pstISRT = connection.prepareStatement(insertElement);
                        pstISRT.setString(1, f.getNomobjet());
                        pstISRT.setInt(2, StockID);
                        pstISRT.setString(3, f.getSerie());
                        pstISRT.setString(4, f.getPhoto1());
                        java.util.Date today =
                                new java.util.Date();

                        java.sql.Date sqlToday =
                                new java.sql.Date(today.getTime());
                        pstISRT.setDate(5, sqlToday);
                        pstISRT.setString(6, f.getModelobjet());

                        pstISRT.setString(7, f.getPhoto2());
                        if (f.getStatus().equals("NORMAL")) {
                            pstISRT.setInt(8, 0);
                        }
                        if (f.getStatus().equals("PERDU")) {
                            pstISRT.setInt(8, 1);
                        }
                        if (f.getStatus().equals("VOLE")) {
                            pstISRT.setInt(8, 2);
                        }
                        pstISRT.setInt(9, 0);
                        pstISRT.setInt(10, i);
                         pstISRT.setString(11, DateAchat);
                        System.out.print("haha" + f.getPhoto1());


                        try {
                            pstISRT.executeUpdate();

                        } catch (SQLException e) {
                            //insert doi tuong vao bang elementtable
                            System.out.print("day la loi" + e.toString());
                        //String insertElement="insert into elementtable(ElementNom,StockID) values ('"+ElementNom+"','"+StockID+"')";
                        //stmt.execute(insertElement);
                        }
                        stmt.executeUpdate("Update paiementtable set Quantite=" + "'" + Quantite + "'where StockID=" + "'" + StockID + "'");

                        valide = 1;
                    } else {
                        valide = 2;
                    }
                } else {
                    valide = 3;
                }
            }
            //btdung
            if (UserRoleID.equals("1")) {
                boolean valide1 = Vst_InserObjet(session, f, UserID);
                System.out.print("insert xong cho visiteur");
                if (valide1) {
                    valide = 3;
                } else {
                    valide = 0;
                }

            }
            // close();
            return valide;
        } catch (Exception ex) {
            ex.printStackTrace();
            return 0;

        }
    }

    public int InserFirstObjet(HttpSession session, ElementBean f, String StockName) {
        try {
            int valide = 0;
            System.out.print("1111111111111111");

            //lay stockID theo UserID va StockName trong bang Paiementtable
            // Chu y la StockName co the trung tuy tung nguoi dung nen phai co UserID
            //check dieu kien o bang Paiement
            //DateDebut, DateFin kiem tra sau
            String UserID = session.getAttribute("UserID").toString();
            String UserRoleID = session.getAttribute("UserRoleID").toString();
            System.out.print("test sesson UserRoleID " + UserRoleID);
            System.out.print("test sesson userID " + UserID);
            if (UserRoleID.equals("1")) {
                Statement stmt = connection.createStatement();
                System.out.print("vao insertObjet");
                String sqltest = "SELECT  ValidePaiement FROM PaiementTable INNER JOIN UserTable ON PaiementTable.UserID = UserTable.UserID ";
                ResultSet srs = stmt.executeQuery(sqltest + "where paiementtable.UserID=" + "'" + UserID + "'");
                int StockID = 0;
                String ValidePaiement = "";
                Date DateDebut = null;
                Date DateFin = null;
                int Quantite = 0;
                boolean valide1 = false;
                while (srs.next()) {

                    ValidePaiement = srs.getString("ValidePaiement");
                    if (ValidePaiement.equals("0")) {
                        valide1 = true;
                    }
                }
                if (!valide1) {

                    //update truong Quantite trong bang paiement len 1

                    Statement stmt2 = connection.createStatement();
                    stmt2.execute("Insert into PaiementTable(UserID,DateDebut,DateFin,ValidePaiement,StockName,Quantite) values('" + UserID + "','2009-01-01','2009-01-01','0','" + f.getSelectid() + "','0')");
                    ResultSet res = stmt2.executeQuery("Select StockID from PaiementTable where UserID='" + UserID + "' and StockID='" + f.getSelectid() + "'");
                    while (res.next()) {
                        StockID = res.getInt("StockID");
                    }
                    String insertElement = "insert into elementtable(ElementNom,StockID,serie,ElementImage1,ElementDateInscription,ElementModel,ElementImage2) values (?,?,?,?,?,?,?)";
                    PreparedStatement pstISRT = connection.prepareStatement(insertElement);
                    pstISRT.setString(1, f.getNomobjet());
                    pstISRT.setInt(2, StockID);
                    pstISRT.setString(3, f.getSerie());
                    pstISRT.setString(4, f.getPhoto1());
                    java.util.Date today =
                            new java.util.Date();

                    java.sql.Date sqlToday =
                            new java.sql.Date(today.getTime());
                    pstISRT.setDate(5, sqlToday);
                    pstISRT.setString(6, f.getModelobjet());

                    pstISRT.setString(7, f.getPhoto2());
                    System.out.print("haha" + f.getPhoto1());


                    try {
                        pstISRT.executeUpdate();
                        valide = 1;
                    } catch (SQLException e) {
                        //insert doi tuong vao bang elementtable
                        System.out.print("day la loi" + e.toString());
                    //String insertElement="insert into elementtable(ElementNom,StockID) values ('"+ElementNom+"','"+StockID+"')";
                    //stmt.execute(insertElement);
                    }

                } else {
                    valide = 2;
                }

            }

            close();
            return valide;
        } catch (Exception ex) {
            ex.printStackTrace();
            return 0;

        }
    }
    //Vst_InserObjet dung cho client visiteur

    public boolean Vst_InserObjet(HttpSession session, ElementBean f, int UserID) {
        try {
            boolean valide = false;

            String username = session.getAttribute("user").toString();

            session.setAttribute("stockname", f.getSelectid());
            System.out.print("UserID & user " + UserID + "   " + username);
            Statement stmt = connection.createStatement();
            // déclarer methode pour inserter a la table paiementtable pour client visiteur
            //String sqlInsert="insert into paiementtable(userID,ValidePaiement,StockName) values('";
            // boolean  valide1=stmt.execute(sqlInsert+UserID+"','0','"+f.getSelectid()+"')");
            String insertElement = "insert into paiementtable(userID,ValidePaiement,StockName,Quantite) values (?,?,?,?)";
            PreparedStatement pstISRT = connection.prepareStatement(insertElement);
            pstISRT.setInt(1, UserID);
            pstISRT.setInt(2, 0);
            pstISRT.setString(3, f.getSelectid());
            pstISRT.setInt(4, 1);
            try {
                pstISRT.executeUpdate();
                valide = true;

            } catch (SQLException e) {
                //insert doi tuong vao bang elementtable
                System.out.print("day la loi1" + e.toString());
                //String insertElement="insert into elementtable(ElementNom,StockID) values ('"+ElementNom+"','"+StockID+"')";
                //stmt.execute(insertElement);
                valide = false;
            }
            // pstISRT.setString(4, f.getPhoto1());
            int StockID1 = 0;
            // boolean valide1=false;
            // System.out.print("kiem tra "+valide1);
            if (valide) {
                System.out.print("kiem tra" + valide);
                ResultSet rel = stmt.executeQuery("select StockID from paiementtable where UserID='" + UserID + "'");
                while (rel.next()) {
                    StockID1 = rel.getInt("StockID");
                    System.out.print("stock moi cho vao la: " + StockID1);
                }
            }
            String an=f.getAn();
            String jour=f.getJour();
            String mois=f.getMois();
            CheckString convert= new CheckString();
            String DateAchat=convert.convertDateAchat(an, mois, jour);
            String insertElement1 = "insert into elementtable(ElementNom,StockID,serie,ElementImage1,ElementDateInscription,ElementModel,ElementImage2,ElementEtat,CategorieID,ElementControl,ElementDateAchat) values (?,?,?,?,?,?,?,?,?,?,?)";
            PreparedStatement pstISRT1 = connection.prepareStatement(insertElement1);
            pstISRT1.setString(1, f.getNomobjet());
            pstISRT1.setInt(2, StockID1);
            pstISRT1.setString(3, f.getSerie());
            pstISRT1.setString(4, f.getPhoto1());
            java.util.Date today =
                    new java.util.Date();
            java.sql.Date sqlToday =
                    new java.sql.Date(today.getTime());
            pstISRT1.setDate(5, sqlToday);
            pstISRT1.setString(6, f.getModelobjet());

            pstISRT1.setString(7, f.getPhoto2());
            System.out.print("Status la: " + f.getStatus());
                         if (f.getStatus().equals("NORMAL")) {
                            pstISRT1.setInt(8, 0);
                            }
                        if (f.getStatus().equals("PERDU")) {
                            pstISRT1.setInt(8, 1);
                        }
                        if (f.getStatus().equals("VOLE")) {
                            pstISRT1.setInt(8, 2);
                        }
          Catalog cata= new Catalog(f.getCatalog());
          int catanumero= cata.getCatalog();
              pstISRT1.setInt(9, catanumero);
              pstISRT1.setBoolean(10, false);
               pstISRT1.setString(11, DateAchat);


            try {
                pstISRT1.executeUpdate();
                valide = true;

            } catch (SQLException e) {
                //insert doi tuong vao bang elementtable
                System.out.print("day la loi " + e.toString());
                //String insertElement="insert into elementtable(ElementNom,StockID) values ('"+ElementNom+"','"+StockID+"')";
                //stmt.execute(insertElement);
                valide = false;
            }

            return valide;

        } catch (SQLException e) {
            //insert doi tuong vao bang elementtable
            System.out.print("day la loi" + e.toString());
            return false;

        }
    }

    /**
     * @return the StockID
     */
    public int getStockID() {
        return StockID;
    }

    /**
     * @param StockID the StockID to set
     */
    public void setStockID(int StockID) {
        this.StockID = StockID;
    }
//statiquer les listes des stockages de client.
    //data entrée: UserLogin
    //data sorti: Resultset contient liste de stockname de UserLogin

    public ResultSet GetListStockageName(String userName) {
        try {
            // List lst = new List();
            Statement stmt = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
            System.out.print("chan 1");
            ResultSet srs = stmt.executeQuery("SELECT StockName FROM PaiementTable INNER JOIN UserTable ON PaiementTable.UserID = UserTable.UserID where UserTable.UserLogin='" + userName + "'");
            System.out.print("chan 2");
            //  close();
            return srs;
        } catch (Exception e) {
            System.out.println(e.getMessage());
            return null;
        }
    }
   public String getFirstStockABN(String userName) {
        try {
            // List lst = new List();
            String first="";
            Statement stmt = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);

            ResultSet srs = stmt.executeQuery("SELECT StockName FROM PaiementTable INNER JOIN UserTable ON PaiementTable.UserID = UserTable.UserID where UserTable.UserLogin='" + userName + "' limit 1");
            while (srs.next()) {
                first=srs.getString("StockName");
            }

            return first;
        } catch (Exception e) {
            System.out.println(e.getMessage());
            return "";
        }
    }

    public String getdatefin(String userName,String stockname) {
        try {
            // List lst = new List();
            String first="";
            Statement stmt = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
System.out.print("oi troi oi 1"+userName);
System.out.print("oi troi oi 2"+stockname);
            ResultSet srs = stmt.executeQuery("SELECT DateFin FROM PaiementTable INNER JOIN UserTable ON PaiementTable.UserID = UserTable.UserID where UserTable.UserLogin='" + userName + "' and StockName='"+stockname+"'");
            while (srs.next()) {
                first=srs.getString("DateFin");
            }
System.out.print("oi troi oi "+first);
            return first;
        } catch (Exception e) {
            System.out.println(e.getMessage());
            return "";
        }
    }

//methode pour statiquer tous les objets dans Stockage de UserLogin

    public ResultSet GetListElement(String StockName, String userName) {
        try {
            // List lst = new List();
            Statement stmt = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
System.out.println("loi");
System.out.println("StockName "+StockName);System.out.println("userName "+userName);
            ResultSet srs = stmt.executeQuery("SELECT *,DateDebut,DateFin,Quantite FROM elementtable INNER JOIN PaiementTable ON ElementTable.StockID = PaiementTable.StockID INNER JOIN UserTable ON PaiementTable.UserID = UserTable.UserID where userlogin='" + userName + "'and StockName='" + StockName + "'");
  System.out.println("qua roi");
            return srs;
        } catch (Exception e) {
            System.out.println("loi ne "+e.getMessage());
            return null;
        }
    }


////methode pour supprimer les objets par ElementID
    public boolean deleteElement(String[] deleterecordElement) {
        try {
            boolean valide = false;
            System.out.print("do dai cua record la " + deleterecordElement.length);
            Statement stmt = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
            ResultSet srs1 = stmt.executeQuery("Select StockID from elementtable where ElementID=" + deleterecordElement[0]);
            int StockID1 = 0;
            while (srs1.next()) {


                StockID1 = srs1.getInt("StockID");
                System.out.print("StockID1 la " + StockID1);
            }

            for (int i = 0; i < deleterecordElement.length; i++) {
                stmt.execute("DELETE FROM elementtable where ElementID='" + deleterecordElement[i] + "'");
                System.out.print("day la " + StockID1 + "thu " + i);
                stmt.execute("Update paiementtable set Quantite=Quantite-1 where StockID='" + StockID1 + "'");
                valide = true;

            }
            close();
            return valide;
        } catch (Exception e) {
            System.out.println("loi o try catch " + e.getMessage());
            return false;
        }
    }
// insert new stockage for user client abonné

    public boolean setNewStockID(String username, String stockName, String max) {
        try {

            System.out.print("them tai khoan");
             System.out.print("username "+username);
             System.out.print("stockName "+stockName);
             System.out.print("max "+max);
            int MaxElement = Integer.parseInt(max);
            System.out.print("max la "+max);
            boolean valide = false;
            Statement stmt = connection.createStatement();
           int userID = -1;
            ResultSet srs = stmt.executeQuery("SELECT UserID FROM usertable where UserLogin='"+username+"'");
while(srs.next())
{
           userID = srs.getInt("UserID");}
            System.out.print("userID   " + userID);
            stmt.executeQuery("insert into paiementtable(userID,validePaiement,StockName,Quantite,MaxElements) values('" + userID + "',0,'" + username + "','1','"+MaxElement+"')");
            close();
            return valide;
        } catch (Exception e) {

            System.out.print("loi  " + e.getMessage());
            return false;
        }
    }

//checkEmailExiste
    //data entrée: mail
    //data sorti:true ou false
    //si true le mail entré existe dans base de donnée
    //si false le mail entré n'existe pas dans base de donnée
    public boolean checkEmailExiste(String mail) {
        try {

            //System.out.print("chay vao day");
            boolean valide = false;
            Statement stmt = connection.createStatement();

            ResultSet srs = stmt.executeQuery("select UserEmail from usertable");
            while (srs.next()) {
                if (srs.getString("UserEmail") != null) {
                    String UserEmail = srs.getString("UserEmail").toString();
                    if (UserEmail.equals(mail)) {
                        valide = true;
                    // System.out.print("check mail " + valide);
                    }
                }
            }
            System.out.print("valide " + valide);
            return valide;

        } catch (Exception e) {

            System.out.print("loi  " + e.getMessage());
            return false;
        }
    }

    //check Email va compte co cung 1 ng ko?
    public boolean checkCompte(String mail,String login) {
        try {

            //System.out.print("chay vao day");
            boolean valide = false;
            Statement stmt = connection.createStatement();

            ResultSet srs = stmt.executeQuery("select UserEmail,UserLogin from usertable");
            while (srs.next()) {
                if (srs.getString("UserEmail") != null&&srs.getString("UserLogin") != null) {
                    String UserEmail = srs.getString("UserEmail").toString();
                    String UserLogin = srs.getString("UserLogin").toString();
                    if (UserEmail.equals(mail)&&UserLogin.equals(login)) {
                        valide = true;
                    // System.out.print("check mail " + valide);
                    }
                }
            }
            System.out.print("valide " + valide);
            return valide;

        } catch (Exception e) {

            System.out.print("loi  " + e.getMessage());
            return false;
        }
    }

    public ResultSet getResultRecherche(String rechercheID, int cateID) {
        try {
            ResultSet srs = null;
            if (cateID != 0) {
                Statement stmt = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
                String sql = "SELECT SousCategorieTable.CategorieID, SousCategorieTable.SousCategorieName,ElementTable.Serie, ElementTable.ElementNom, ElementTable.ElementEtat, ElementTable.ElementDateAchat,ElementTable.ElementDateInscription, ElementTable.ElementImage1,ElementTable.ElementImage2, ElementTable.ElementModel, UserTable.UserNom, UserTable.UserPrenom,ElementTable.StockID FROM ElementTable INNER JOIN PaiementTable ON ElementTable.StockID = PaiementTable.StockID INNER JOIN SousCategorieTable ON ElementTable.SousCategorieID = SousCategorieTable.SousCategorieID INNER JOIN UserTable ON PaiementTable.UserID = UserTable.UserID where SousCategorieTable.CategorieID='" + cateID + "'&& ElementTable.ElementNom like '%" + rechercheID + "%'";
                srs = stmt.executeQuery(sql);
            //  ResultSet srs = stmt.executeQuery("SELECT SousCategorieTable.CategorieID, SousCategorieTable.SousCategorieName,ElementTable.Serie, ElementTable.ElementNom, ElementTable.ElementEtat, ElementTable.ElementDateAchat, ElementTable.ElementDateInscription, ElementTable.ElementImage1, UserTable.UserNom, UserTable.UserPrenom,ElementTable.StockID FROM ElementTable INNER JOIN PaiementTable ON ElementTable.StockID = PaiementTable.StockID INNER JOIN PaiementTable ON ElementTable.StockID = PaiementTable.StockID INNER JOIN SousCategorieTable ON ElementTable.SousCategorieID = SousCategorieTable.SousCategorieID INNER JOIN UserTable ON PaiementTable.UserID = UserTable.UserID where SousCategorieTable.CategorieID="+cateID);

            } else {
                Statement stmt = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
                String sql = "SELECT SousCategorieTable.CategorieID, SousCategorieTable.SousCategorieName,ElementTable.Serie, ElementTable.ElementNom, ElementTable.ElementEtat, ElementTable.ElementDateAchat,ElementTable.ElementDateInscription, ElementTable.ElementImage1,ElementTable.ElementImage2, ElementTable.ElementModel, UserTable.UserNom, UserTable.UserPrenom,ElementTable.StockID FROM ElementTable INNER JOIN PaiementTable ON ElementTable.StockID = PaiementTable.StockID INNER JOIN SousCategorieTable ON ElementTable.SousCategorieID = SousCategorieTable.SousCategorieID INNER JOIN UserTable ON PaiementTable.UserID = UserTable.UserID where ElementTable.ElementNom like '%" + rechercheID + "%'";
                srs = stmt.executeQuery(sql);
            }
            return srs;
        } catch (Exception e) {

            System.out.print("loi trong qua trinh tim kiem la " + e.getMessage());
            return null;
        }
    }
    //get email from user dau vao la userlogin

    public String getemail(String user) {
        try {
            ResultSet srs = null;
            String email = "";
            Statement stmt = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);

            srs = stmt.executeQuery("SELECT UserEmail FROM usertable where UserLogin='" + user + "' ");
            while (srs.next()) {
                email = srs.getString("UserEmail");
            }
            return email;
        } catch (Exception e) {

            System.out.print("loi trong qua trinh tim kiem la " + e.getMessage());
            return null;
        }
    }
     public String getLogin(String user) {
        try {
            ResultSet srs = null;
            String email = "";
            Statement stmt = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);

            srs = stmt.executeQuery("SELECT UserEmail FROM usertable where UserLogin='" + user + "' ");
            while (srs.next()) {
                email = srs.getString("UserEmail");
            }
            return email;
        } catch (Exception e) {

            System.out.print("loi trong qua trinh tim kiem la " + e.getMessage());
            return null;
        }
    }
//dang lam

    public ResultSet CheckObjectBySerie(String serie) {
        try {
            ResultSet srs = null;
            Statement stmt = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
            String sql = "SELECT  ElementTable.*, UserTable.UserNom, UserTable.UserPrenom,UserTable.UserEmail FROM PaiementTable INNER JOIN ElementTable ON PaiementTable.StockID = ElementTable.StockID INNER JOIN UserTable ON PaiementTable.UserID =UserTable.UserID INNER JOIN CategorieTable ON ElementTable.CategorieID = CategorieTable.CategorieID where ElementTable.Serie='"+serie+ "'";
            srs = stmt.executeQuery(sql);
            return srs;
        } catch (Exception e) {

            System.out.print("loi trong qua trinh tim kiem la " + e.getMessage());
            return null;
        }
    }
    //kiem tra so lan User dang search san pham tuong ung
    //tu do biet duoc so thu se gui den CL ABN

    public int GetnumeroSearch(int elementid, int userid) {
        try {
            int nomero = 0;
            Statement stmt = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
            String sql = "";
            ResultSet srs = stmt.executeQuery("select NumeroSearch from SearchTable where ElementID='" + elementid + "' and UserID='" + userid + "'");
            if (srs != null) {
                while (srs.next()) {
                    nomero = srs.getInt("NumeroSearch");
                }
            System.out.print("so lan thang nay no search la: "+nomero);
                return nomero;
            } else {
                return 0;
            }
        } catch (Exception e) {

            System.out.print("loi trong qua trinh tim kiem la " + e.getMessage());
            return 0;
        }
    }

    //insertSearchTable
    public boolean insertSearchTable(int elementid, int userid) {
        try {
            boolean valide = false;
            phan.connection.DateUtils today =new phan.connection.DateUtils();
            System.out.print("qua trinh  " + elementid+userid);
            String SQLDay=today.now();
            Statement stmt = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
            valide = stmt.execute("insert into SearchTable(ElementID,UserID,NumeroSearch,DateVerification) values('"+elementid+"','"+userid +"','1','"+SQLDay+"')");
            return valide;
        } catch (Exception e) {

            System.out.print("loi trong qua trinh tim kiem la " + e.getMessage());
            return false;
        }
    }

    public int UpdateSearchTable(int elementid, int userid) {
        try {
            int valide = 0;
            Statement stmt = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
            DateUtils today =new DateUtils();
            String SQLDay=today.now();
            valide = stmt.executeUpdate("Update SearchTable set NumeroSearch=NumeroSearch+1, DateVerification='"+SQLDay+"' where ElementID='" + elementid + "'and UserID='" + userid + "'");
            return valide;
        } catch (Exception e) {

            System.out.print("loi trong qua trinh UPDATE  la " + e.getMessage());
            return 0;
        }
    }

    public ResultSet getOublierLogin(String email) {
        try {
            ResultSet src = null;
            Statement stmt = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
            src = stmt.executeQuery("select UserLogin, UserPassword,UserNom,UserPrenom from UserTable where UserEmail='" + email + "'");


            return src;
        } catch (Exception e) {

            System.out.print("loi trong qua trinh tim kiem la " + e.getMessage());
            return null;
        }
    }

    public boolean checkAjouter(int userid) {
        try {
            boolean valide = false;
            ResultSet src = null;
            Statement stmt = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
            src = stmt.executeQuery("select * from PaiementTable where userID='" + userid + "'");
            System.out.print("src la " + src);
            while (src.next()) {
                int userid1 = src.getInt("userID");
                System.out.print("userID la" + userid1);
                valide = true;
            }
            System.out.print("valide la" + valide);


            //valide=false;
            return valide;
        } catch (Exception e) {

            System.out.print("loi trong qua trinh tim kiem la " + e.getMessage());
            return false;
        }

    }
    //check numero de serie

    public ResultSet checkNumeroDeSerie(String Serie) {
        try {

            ResultSet src = null;
            Statement stmt = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
            src = stmt.executeQuery("SELECT   ElementID,serie, UserTable.UserNom, UserTable.UserPrenom, UserTable.UserEmail, ElementTable.ElementNom,CategorieTable.CategorieNom, ElementTable.ElementDateInscription, ElementTable.ElementEtat FROM CategorieTable INNER JOIN ElementTable ON CategorieTable.CategorieID = ElementTable.CategorieID INNER JOIN PaiementTable ON ElementTable.StockID =PaiementTable.StockID INNER JOIN UserTable ON PaiementTable.UserID =UserTable.UserID where serie='" + Serie + "' ORDER BY ElementID LIMIT 1");




            //valide=false;
            return src;
        } catch (Exception e) {

            System.out.print("loi trong qua trinh tim kiem la " + e.getMessage());
            return null;
        }
    }
    //check xem VST da dang ky san pham hay chua

    public boolean checkAjouterVST(int userid) {
        try {
            boolean valide = false;
            ResultSet src = null;
            Statement stmt = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
            src = stmt.executeQuery("select * from PaiementTable where userID='" + userid + "'");
            System.out.print("src la " + src);
            while (src.next()) {
                int userid1 = src.getInt("ValidePaiement");
                if (userid1 == 0) {
                    valide = true;
                }
            }
            System.out.print("valide la" + valide);


            //valide=false;
            return valide;
        } catch (Exception e) {

            System.out.print("loi trong qua trinh tim kiem la " + e.getMessage());
            return false;
        }

    }
    // insert new stockage for user client abonné

    public boolean UpdateStockID(String username, String stockName, String max,String typePaiement) {
        try {

            System.out.print("them tai khoan");

            boolean valide = false;
            Statement stmt = connection.createStatement();

            fixLien fl = new fixLien();
            int MaxElement = fl.MaxElement(max);
            int userID = 0;
            ResultSet srs = stmt.executeQuery("SELECT UserID FROM usertable where UserLogin='" + username + "'");
            while (srs.next()) {
                userID = srs.getInt("UserID");
            }

            System.out.print("userID   " + userID);
            stmt.execute("update  paiementtable set MaxElements=" + MaxElement + ",TypePaiement='"+typePaiement+"' where UserID=" + userID + " and StockName='" + stockName + "'");
            close();
            return valide;
        } catch (Exception e) {

            System.out.print("loi  " + e.getMessage());
            return false;
        }
    }
    

    public ResultSet TotalElement(String stockageName, int userid) {
        try {

            int src = 0;
            Statement stmt = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
            ResultSet result = stmt.executeQuery("SELECT  Quantite,ValidePaiement,MaxElements  from PaiementTable where UserID=" + userid + " and Stockname='" + stockageName + "'");

            return result;
        } catch (Exception e) {

            System.out.print("loi trong qua trinh tim kiem la " + e.getMessage());
            return null;
        }
    }
    //lay thong tin user de modifier

    public ResultSet getInforUser(String user) {
        try {

            int src = 0;
            Statement stmt = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
            ResultSet result = stmt.executeQuery("SELECT  * from usertable where UserLogin='" + user + "'");

            return result;
        } catch (Exception e) {

            System.out.print("loi trong qua trinh tim kiem la " + e.getMessage());
            return null;
        }
    }

    //check pass cua nguoi dung
    public boolean checkMotDePass(String user, String pass) {
        try {
            boolean valide = false;
            Statement stmt = connection.createStatement();
            ResultSet srs = stmt.executeQuery("SELECT UserPassword FROM usertable where UserLogin='" + user + "'");
            while (srs.next()) {
                String password = srs.getString("UserPassword");
                if (pass.equals(password)) {
                    valide = true;
                }
            }
            close();
            return valide;
        } catch (Exception e) {

            System.out.print("loi  " + e.getMessage());
            return false;
        }

    }

    public boolean setNewPass(String user, String Newpass) {
        try {

            Statement stmt = connection.createStatement();
            boolean valide = stmt.execute("Update usertable set UserPassword='" + Newpass + "' where UserLogin='" + user + "'");
            System.out.print("valide o day la:" + valide);
            close();
            return true;
        } catch (Exception e) {

            System.out.print("loi  " + e.getMessage());
            return false;
        }
    }

    public boolean UpdateInformation(String login, ModifierCoordonneesBean f, String birthday) {
        try {
            System.out.print("kiem tra ten thay doi: " + f.getNom());
            Statement stmt = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
            boolean valide = stmt.execute("Update usertable set UserNom='" + f.getNom() + "',UserPreNom='" + f.getPrenom() + "',UserEmail='" + f.getEmail() + "',UserAdresse='" + f.getAdress() + "',UserVille='" + f.getVille() + "',UserCode='" + f.getPostal() + "',UserPays='" + f.getPays() + "',UserTelephone='" + f.getTel() + "',birthday='" + birthday + "'where UserLogin='" + login + "'");
            System.out.print("valide o day la:" + valide);
            close();
            return true;
        } catch (Exception e) {

            System.out.print("loi  " + e.getMessage());
            return false;
        }
    }
    // con.UpdateStatusElement(ElementID[j],record[j]);

    public boolean UpdateStatusElement(String ID, String Status) {
        try {
            int ElementEtat = 0;
            if (Status.equals("NORMAL")) {
                ElementEtat = 0;
            }
            if (Status.equals("PERDU")) {
                ElementEtat = 1;
            }
            if (Status.equals("VOLE")) {
                ElementEtat = 2;
            }
            Statement stmt = connection.createStatement();
            String convert = "";
            for (int i = 0; i < ID.length(); i++) {
                if (ID.charAt(i) == '0' || ID.charAt(i) == '1' || ID.charAt(i) == '2' || ID.charAt(i) == '3' || ID.charAt(i) == '4' || ID.charAt(i) == '5' || ID.charAt(i) == '6' || ID.charAt(i) == '7' || ID.charAt(i) == '8' || ID.charAt(i) == '9') {
                    convert = convert + ID.charAt(i);
                }
            }
 java.util.Date today =
                                new java.util.Date();

                        java.sql.Date sqlToday =
                                new java.sql.Date(today.getTime());
            boolean valide = stmt.execute("Update elementtable set ElementEtat='" + ElementEtat + "',ElementDateModifier='"+sqlToday+"' where ElementID='" + convert + "'");

            close();
            return true;
        } catch (Exception e) {

            System.out.print("loi  " + e.getMessage());
            return false;
        }
    }


    // src=  connector.GetElementInfos(record[i]);
    public ResultSet GetElementInfos(String recordid) {
        try {

            int src = 0;
            Statement stmt = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
            ResultSet result = stmt.executeQuery("SELECT  * from elementtable where ElementID='" + recordid + "'");

            return result;
        } catch (Exception e) {

            System.out.print("loi trong qua trinh tim kiem la " + e.getMessage());
            return null;
        }
    }
    // boolean check =  con.checkStockageName(user,selectid);

    public boolean checkStockageName(String user, String selectid) {
        try {
                boolean valide=false;
            Statement stmt = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
            ResultSet result = stmt.executeQuery("SELECT StockName FROM PaiementTable INNER JOIN UserTable ON PaiementTable.UserID = UserTable.UserID where UserLogin='" + user + "' and Stockname='" + selectid + "'");
            while (result.next()) {
                String StockName = result.getString("StockName");
                if (StockName.equals(selectid)) {
                    valide= true;
                }
            }

            close();
             return  valide;
        } catch (Exception e) {

            System.out.print("loi  " + e.getMessage());
            return false;
        }
    }
   /**  ANH HIEU LAM NGAY  HOM NAY 15/62009*/
    public String  getNomObjetFromSerie(String serie) {

        try {
            ResultSet srs = null;
            String NomObjet = "";
            Statement stmt = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);

            srs = stmt.executeQuery("SELECT  ElementNom from elementtable where serie='" + serie + "' ");
            while (srs.next()) {
                NomObjet = srs.getString("ElementNom");
            }
            return NomObjet;
        } catch (Exception e) {

            System.out.print("loi trong qua trinh tim kiem la " + e.getMessage());
            return null;
        }
    }
    
 
     public int  getEtatObjetFromSerie(String serie) {

        try {
            ResultSet srs = null;
            int etat=-1 ;
            Statement stmt = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);

            srs = stmt.executeQuery("SELECT  ElementEtat from elementtable where serie='" + serie + "' ");
            while (srs.next()) {
                etat = Integer.parseInt(srs.getString("ElementEtat"));
            }
            return etat;
        } catch (Exception e) {

            System.out.print("loi trong qua trinh tim kiem la " + e.getMessage());
            return -1;
        }
    }

      public int  getUserIDProprietaireFromSerie(String serie) {

        try {
            ResultSet srs = null;
            int ID=-1;
            Statement stmt = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);

            srs = stmt.executeQuery("SELECT  UserID from PaiementTable INNER JOIN  elementtable ON PaiementTable.StockID = elementtable.StockID where serie='" + serie + "' ");
            
            while (srs.next()) {
                ID = srs.getInt("UserID");
            }
            return ID;
        } catch (Exception e) {

            System.out.print("loi trong qua trinh tim kiem la " + e.getMessage());
            return -1;
        }
    }

      public ResultSet GetProprietaireInfos(int userid) {
        try {

            
            Statement stmt = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
            ResultSet result = stmt.executeQuery("SELECT  * from usertable where UserID='" + userid + "'");

            return result;
        } catch (Exception e) {

            System.out.print("loi trong qua trinh tim kiem la " + e.getMessage());
            return null;
        }
    }

      public boolean  insertInfosRecherche(String serie,String sLogin, String pLogin,String nom,int etat,String date) {
        try {
            boolean check=false;
            Statement stmt = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
        //    check=stmt.execute("insert into inforecherchetable(serie,RechercheurLogin,ProprietaireLogin,NomObjet,EtatObjet,DateVerification) values('"+serie+"','"+sLogin+"','"+pLogin+",'"+nom+"' , '"+etat+"' ,'"+date+"')");
            String insertInfo ="insert into inforecherchetable(serie,RechercheurLogin,ProprietaireLogin,NomObjet,EtatObjet,DateVerification) " +
            "values ('"+serie+"','"+sLogin+"','"+pLogin+"','"+nom+"','"+etat+"','"+date+"')";
            check=stmt.execute(insertInfo);
            return check;
           
        } catch (Exception e) {

            System.out.print("loi trong qua trinh tim kiem la " + e.getMessage());
            return false;
        }
    }
     public ResultSet getMaxIDfrolUser(String user){
             try {
            
            Statement stmt = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
        //    check=stmt.execute("insert into inforecherchetable(serie,RechercheurLogin,ProprietaireLogin,NomObjet,EtatObjet,DateVerification) values('"+serie+"','"+sLogin+"','"+pLogin+",'"+nom+"' , '"+etat+"' ,'"+date+"')");
            String setMax ="SELECT  max(ElementID) FROM ElementTable INNER JOIN PaiementTable ON ElementTable.StockID = PaiementTable.StockID INNER JOIN UserTable ON PaiementTable.UserID =UserTable.UserID where UserLogin='"+user+"'";
                int MAxElementID=0;
            ResultSet srx1 = stmt.executeQuery(setMax);
                 while (srx1.next()) {
                     MAxElementID=srx1.getInt("max(ElementID)");
                 }
           String setMax1 ="SELECT  ElementID,ElementImage1,ElementImage2 FROM ElementTable INNER JOIN PaiementTable ON ElementTable.StockID = PaiementTable.StockID INNER JOIN UserTable ON PaiementTable.UserID =UserTable.UserID where UserLogin='"+user+"' and ElementID='"+MAxElementID+"'";
           ResultSet srx = stmt.executeQuery(setMax1);
            return srx;

        } catch (Exception e) {

            System.out.print("loi trong qua trinh tim kiem la " + e.getMessage());
            return null;
        }
     }

              public boolean updateImageName(int ElementID) {
                try {
                    boolean valide=false;
            Statement stmt = connection.createStatement();


                stmt.executeUpdate("Update elementtable set ElementImage1='"+ElementID+"-1.jpg"+"',ElementImage2='"+ElementID+"-2.jpg"+"'where ElementID='" + ElementID + "'");
                valide = true;

          //  close();
            return valide;
        } catch (Exception ex) {
            ex.printStackTrace();
            return false;

        }
    }

       public boolean   updateNumeroCommand(String num,int userid) {
                try {
                    boolean valide=false;
                    Statement stmt = connection.createStatement();
                    stmt.executeUpdate("Update paiementtable set NumeroCommand='"+num+"' where UserID='"+userid+"' ");
                    valide = true;

          //  close();
            return valide;
        } catch (Exception ex) {
            ex.printStackTrace();
            return false;

        }
    }
	 public int getStockidFromUserId(int userid,String stockname){

            try {
            ResultSet srs = null;
            int id=-1 ;
            Statement stmt = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);

            srs = stmt.executeQuery("SELECT  StockID from paiementtable where UserID='"+userid+"' and StockName='"+stockname+"'");
            while (srs.next()) {
                id=srs.getInt("StockID");
            }
            return id;
        } catch (Exception e) {

            System.out.print("loi trong qua trinh tim kiem la fonction getStockidFromUserId  " + e.getMessage());
            return -1;
        }
       }
      public ResultSet get10objet() {
        try {

            DateUtils today = new DateUtils();
            String sqltoday = today.now();
            Statement stmt = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
            ResultSet result = stmt.executeQuery("SELECT * FROM Elementtable"+
                    " where ElementDateInscription BETWEEN '1900-01-01' AND '"+sqltoday+"'"+
 " order by ElementDateInscription desc,ElementID desc"+
 " limit 10");

            return result;
        } catch (Exception e) {

            System.out.print("loi trong qua trinh tim kiem la " + e.getMessage());
            return null;
        }
    }

      

}
